﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for ApplicantDetailsHospitalDAL
/// </summary>
public class ApplicantDetailsHospitalDAL
{
	public ApplicantDetailsHospitalDAL()
	{
	
	}

    public DataSet getSelfDetail(int applicationID){

        SqlConnection con = DBManager.getSqlConnection();

        String sql = "SELECT A.applicationID,A.custID,A.paymentPeriod,A.startDate,A.endDate, I.insuredId, I.applicationInsuranceId, I.fName, I.lName, I.NRIC, I.nationality, case I.gender when 0 then 'Male' else 'Female' end as gender, I.marital, I.birth, I.relationship, I.planType, I.occupation," + 
                     "I.telephone, I.longStay, I.mobile, I.email, I.address, I.postalCode, I.employerName, I.employerAddress, I.business, I.qstDisease, I.illness, I.illDuration, I.illTreatment, " +
                     "I.illhospital, I.DeclinedHistory, I.foreseeable, I.bloodPressureIllSystolic, I.bloodPressureIllDate, I.bloodPressureIllHospital, I.HyperlipidaemiaLevel, " +
                     "I.HyperlipidaemiaDate, I.HyperlipidaemiaHospital, I.height, I.weight, I.isSmoker, I.qstTravelFrequency, I.qstHazardous" +
                     " FROM  APPLICATION_INSURANCE AS A  INNER JOIN" +
                     " APPLICATION_INSURANCE_INSURED AS I ON A.applicationID = I.applicationInsuranceId" +
                     " WHERE I.relationship ='SELF'AND A.applicationID =@applicationID ";
        
        SqlCommand cmd = new SqlCommand(sql, con);
        cmd.Parameters.AddWithValue("@applicationID", applicationID);

        SqlDataAdapter myAdapter = new SqlDataAdapter(cmd);

        DataSet myDs = new DataSet();

        myAdapter.Fill(myDs,"SelfDetail");

        return myDs;
    }

    public DataSet getSpouseDetail(int applicationID)
    {

        SqlConnection con = DBManager.getSqlConnection();

        String sql = "SELECT A.applicationID,A.custID,A.paymentPeriod, I.insuredId, I.applicationInsuranceId, I.fName, I.lName, I.NRIC, I.nationality, case I.gender when 0 then 'Male' else 'Female' end as gender, I.marital, I.birth, I.relationship, I.planType, I.occupation," +
                     "I.telephone, I.longStay, I.mobile, I.email, I.address, I.postalCode, I.employerName, I.employerAddress, I.business, I.qstDisease, I.illness, I.illDuration, I.illTreatment, " +
                     "I.illhospital, I.DeclinedHistory, I.foreseeable, I.bloodPressureIllSystolic, I.bloodPressureIllDate, I.bloodPressureIllHospital, I.HyperlipidaemiaLevel, " +
                     "I.HyperlipidaemiaDate, I.HyperlipidaemiaHospital, I.height, I.weight, case I.isSmoker when 0 then 'Yes' else 'No' end as isSmoker, I.qstTravelFrequency, I.qstHazardous" +
                     " FROM  APPLICATION_INSURANCE AS A INNER JOIN" +
                     " APPLICATION_INSURANCE_INSURED AS I ON A.applicationID = I.applicationInsuranceId" +
                     " WHERE I.relationship ='SPOUSE'AND A.applicationID =@applicationID ";

        SqlCommand cmd = new SqlCommand(sql, con);
        cmd.Parameters.AddWithValue("@applicationID", applicationID);


        SqlDataAdapter myAdapter = new SqlDataAdapter(cmd);

        DataSet myDs = new DataSet();

        con.Open();
        myAdapter.Fill(myDs,"SpouseDetail");

        con.Close();
        return myDs;

    }

    public DataSet getChildrenDetail(int applicationID)
    {
        SqlConnection con = DBManager.getSqlConnection();

        String sql = "SELECT A.applicationID,A.custID,A.paymentPeriod, I.insuredId, I.applicationInsuranceId, I.fName, I.lName, I.NRIC, I.nationality, case I.gender when 0 then 'Male' else 'Female' end as gender, I.marital, I.birth, I.relationship, I.planType, I.occupation," +
                     "I.telephone, I.longStay, I.mobile, I.email, I.address, I.postalCode, I.employerName, I.employerAddress, I.business, I.qstDisease, I.illness, I.illDuration, I.illTreatment, " +
                     "I.illhospital, I.DeclinedHistory, I.foreseeable, I.bloodPressureIllSystolic, I.bloodPressureIllDate, I.bloodPressureIllHospital, I.HyperlipidaemiaLevel, " +
                     "I.HyperlipidaemiaDate, I.HyperlipidaemiaHospital, I.height, I.weight, case I.isSmoker when 0 then 'Yes' else 'No' end as isSmoker, I.qstTravelFrequency, I.qstHazardous" +
                     " FROM  APPLICATION_INSURANCE AS A INNER JOIN" +
                     " APPLICATION_INSURANCE_INSURED AS I ON A.applicationID = I.applicationInsuranceId" +
                     " WHERE I.relationship ='CHILD'AND A.applicationID =@applicationID ";

        SqlCommand cmd = new SqlCommand(sql, con);
        cmd.Parameters.AddWithValue("@applicationID", applicationID);


        SqlDataAdapter myAdapter = new SqlDataAdapter(cmd);

        DataSet myDs = new DataSet();

        con.Open();
        myAdapter.Fill(myDs,"ChildrenDetail");

        con.Close();
        return myDs;

    }


    public DataSet getFamilyDetail(int applicationId)
    {
        SqlConnection con = DBManager.getSqlConnection();

        String sql = "SELECT A.applicationID,A.custID,A.paymentPeriod, I.insuredId, I.applicationInsuranceId, I.fName, I.lName, I.NRIC, I.nationality, case I.gender when 0 then 'Male' else 'Female' end as gender, I.marital, I.birth, I.relationship, I.planType, I.occupation," +
                     "I.telephone, I.longStay, I.mobile, I.email, I.address, I.postalCode, I.employerName, I.employerAddress, I.business, I.qstDisease, I.illness, I.illDuration, I.illTreatment, " +
                     "I.illhospital, I.DeclinedHistory, I.foreseeable, I.bloodPressureIllSystolic, I.bloodPressureIllDate, I.bloodPressureIllHospital, I.HyperlipidaemiaLevel, " +
                     "I.HyperlipidaemiaDate, I.HyperlipidaemiaHospital, I.height, I.weight, case I.isSmoker when 0 then 'Yes' else 'No' end as isSmoker, I.qstTravelFrequency, I.qstHazardous" +
                     " FROM  APPLICATION_INSURANCE AS A INNER JOIN" +
                     " APPLICATION_INSURANCE_INSURED AS I ON A.applicationID = I.applicationInsuranceId" +
                     " WHERE I.relationship !='SELF'AND A.applicationID =@applicationID ";

        SqlCommand cmd = new SqlCommand(sql, con);
        cmd.Parameters.AddWithValue("@applicationID", applicationId);


        SqlDataAdapter myAdapter = new SqlDataAdapter(cmd);

        DataSet myDs = new DataSet();
        myAdapter.Fill(myDs,"FamilyDetail");

        return myDs;
    }

    public DataSet getIllnessList(int applicationId)
    {
        SqlConnection con = DBManager.getSqlConnection();

        String sql = "SELECT A.applicationID,A.paymentPeriod,A.custID, I.insuredId, I.applicationInsuranceId, I.fName, I.lName, I.NRIC, I.nationality, case I.gender when 0 then 'Male' else 'Female' end as gender, I.marital, I.birth, I.relationship, I.planType, I.occupation," +
                     "I.telephone, I.longStay, I.mobile, I.email, I.address, I.postalCode, I.employerName, I.employerAddress, I.business, I.qstDisease, I.illness, I.illDuration, I.illTreatment, " +
                     "I.illhospital, I.DeclinedHistory, I.foreseeable, I.bloodPressureIllSystolic, I.bloodPressureIllDate, I.bloodPressureIllHospital, I.HyperlipidaemiaLevel, " +
                     "I.HyperlipidaemiaDate, I.HyperlipidaemiaHospital, I.height, I.weight, case I.isSmoker when 0 then 'Yes' else 'No' end as isSmoker, I.qstTravelFrequency, I.qstHazardous" +
                     " FROM  APPLICATION_INSURANCE AS A INNER JOIN" +
                     " APPLICATION_INSURANCE_INSURED AS I ON A.applicationID = I.applicationInsuranceId" +
                     " WHERE I.illness <> null AND A.applicationID =@applicationID ";

        SqlCommand cmd = new SqlCommand(sql, con);
        cmd.Parameters.AddWithValue("@applicationID", applicationId);


        SqlDataAdapter myAdapter = new SqlDataAdapter(cmd);

        DataSet myDs = new DataSet();
        myAdapter.Fill(myDs,"IllnessList");

        return myDs;
    }

    public DataSet getForeseeableList(int applicationId)
    {
        SqlConnection con = DBManager.getSqlConnection();

        String sql = "SELECT A.applicationID,A.paymentPeriod,A.custID, I.insuredId, I.applicationInsuranceId, I.fName, I.lName, I.NRIC, I.nationality, case I.gender when 0 then 'Male' else 'Female' end as gender, I.marital, I.birth, I.relationship, I.planType, I.occupation," +
                     "I.telephone, I.longStay, I.mobile, I.email, I.address, I.postalCode, I.employerName, I.employerAddress, I.business, I.qstDisease, I.illness, I.illDuration, I.illTreatment, " +
                     "I.illhospital, I.DeclinedHistory, I.foreseeable, I.bloodPressureIllSystolic, I.bloodPressureIllDate, I.bloodPressureIllHospital, I.HyperlipidaemiaLevel, " +
                     "I.HyperlipidaemiaDate, I.HyperlipidaemiaHospital, I.height, I.weight, case I.isSmoker when 0 then 'Yes' else 'No' end as isSmoker, I.qstTravelFrequency, I.qstHazardous" +
                     " FROM  APPLICATION_INSURANCE AS A INNER JOIN" +
                     " APPLICATION_INSURANCE_INSURED AS I ON A.applicationID = I.applicationInsuranceId" +
                     " WHERE I.foreseeable <> null AND A.applicationID =@applicationID ";

        SqlCommand cmd = new SqlCommand(sql, con);
        cmd.Parameters.AddWithValue("@applicationID", applicationId);


        SqlDataAdapter myAdapter = new SqlDataAdapter(cmd);

        DataSet myDs = new DataSet();
        myAdapter.Fill(myDs,"ForeseeableList");

        return myDs;
    }

    public DataSet getBloodPressureList(int applicationId)
    {
        SqlConnection con = DBManager.getSqlConnection();

        String sql = "SELECT A.applicationID,A.paymentPeriod,A.custID, I.insuredId, I.applicationInsuranceId, I.fName, I.lName, I.NRIC, I.nationality, case I.gender when 0 then 'Male' else 'Female' end as gender, I.marital, I.birth, I.relationship, I.planType, I.occupation," +
                     "I.telephone, I.longStay, I.mobile, I.email, I.address, I.postalCode, I.employerName, I.employerAddress, I.business, I.qstDisease, I.illness, I.illDuration, I.illTreatment, " +
                     "I.illhospital, I.DeclinedHistory, I.foreseeable, I.bloodPressureIllSystolic, I.bloodPressureIllDate, I.bloodPressureIllHospital, I.HyperlipidaemiaLevel, " +
                     "I.HyperlipidaemiaDate, I.HyperlipidaemiaHospital, I.height, I.weight, case I.isSmoker when 0 then 'Yes' else 'No' end as isSmoker, I.qstTravelFrequency, I.qstHazardous" +
                     " FROM  APPLICATION_INSURANCE AS A INNER JOIN" +
                     " APPLICATION_INSURANCE_INSURED AS I ON A.applicationID = I.applicationInsuranceId" +
                     " WHERE I.bloodPressureIllHospital <> null AND A.applicationID =@applicationID ";

        SqlCommand cmd = new SqlCommand(sql, con);
        cmd.Parameters.AddWithValue("@applicationID", applicationId);


        SqlDataAdapter myAdapter = new SqlDataAdapter(cmd);

        DataSet myDs = new DataSet();
        myAdapter.Fill(myDs,"BloodPressureList");

        return myDs;
    }


    public DataSet getHyperlipidaemiaList(int applicationId)
    {
        SqlConnection con = DBManager.getSqlConnection();

        String sql = "SELECT A.applicationID,A.paymentPeriod,A.custID, I.insuredId, I.applicationInsuranceId, I.fName, I.lName, I.NRIC, I.nationality, case I.gender when 0 then 'Male' else 'Female' end as gender, I.marital, I.birth, I.relationship, I.planType, I.occupation," +
                     "I.telephone, I.longStay, I.mobile, I.email, I.address, I.postalCode, I.employerName, I.employerAddress, I.business, I.qstDisease, I.illness, I.illDuration, I.illTreatment, " +
                     "I.illhospital, I.DeclinedHistory, I.foreseeable, I.bloodPressureIllSystolic, I.bloodPressureIllDate, I.bloodPressureIllHospital, I.HyperlipidaemiaLevel, " +
                     "I.HyperlipidaemiaDate, I.HyperlipidaemiaHospital, I.height, I.weight, case I.isSmoker when 0 then 'Yes' else 'No' end as isSmoker, I.qstTravelFrequency, I.qstHazardous" +
                     " FROM  APPLICATION_INSURANCE AS A INNER JOIN" +
                     " APPLICATION_INSURANCE_INSURED AS I ON A.applicationID = I.applicationInsuranceId" +
                     " WHERE I.HyperlipidaemiaHospital <> null AND A.applicationID =@applicationID ";

        SqlCommand cmd = new SqlCommand(sql, con);
        cmd.Parameters.AddWithValue("@applicationID", applicationId);


        SqlDataAdapter myAdapter = new SqlDataAdapter(cmd);

        DataSet myDs = new DataSet();
        myAdapter.Fill(myDs,"HyperlipidaemiaList");

        return myDs;
    }

    public DataSet getHazardousList(int applicationId)
    {
        SqlConnection con = DBManager.getSqlConnection();

        String sql = "SELECT A.applicationID,A.paymentPeriod,A.custID, I.insuredId, I.applicationInsuranceId, I.fName, I.lName, I.NRIC, I.nationality, case I.gender when 0 then 'Male' else 'Female' end as gender, I.marital, I.birth, I.relationship, I.planType, I.occupation," +
                     "I.telephone, I.longStay, I.mobile, I.email, I.address, I.postalCode, I.employerName, I.employerAddress, I.business, I.qstDisease, I.illness, I.illDuration, I.illTreatment, " +
                     "I.illhospital, I.DeclinedHistory, I.foreseeable, I.bloodPressureIllSystolic, I.bloodPressureIllDate, I.bloodPressureIllHospital, I.HyperlipidaemiaLevel, " +
                     "I.HyperlipidaemiaDate, I.HyperlipidaemiaHospital, I.height, I.weight, case I.isSmoker when 0 then 'Yes' else 'No' end as isSmoker, I.qstTravelFrequency, I.qstHazardous" +
                     " FROM  APPLICATION_INSURANCE AS A INNER JOIN" +
                     " APPLICATION_INSURANCE_INSURED AS I ON A.applicationID = I.applicationInsuranceId" +
                     " WHERE I.qstHazardous <> null AND A.applicationID =@applicationID ";

        SqlCommand cmd = new SqlCommand(sql, con);
        cmd.Parameters.AddWithValue("@applicationID", applicationId);


        SqlDataAdapter myAdapter = new SqlDataAdapter(cmd);

        DataSet myDs = new DataSet();
        myAdapter.Fill(myDs, "HazardousList");

        return myDs;
    }

    public DataSet getTravelFrequencyList(int applicationId)
    {
        SqlConnection con = DBManager.getSqlConnection();

        String sql = "SELECT A.applicationID,A.paymentPeriod,A.custID, I.insuredId, I.applicationInsuranceId, I.fName, I.lName, I.NRIC, I.nationality, case I.gender when 0 then 'Male' else 'Female' end as gender, I.marital, I.birth, I.relationship, I.planType, I.occupation," +
                     "I.telephone, I.longStay, I.mobile, I.email, I.address, I.postalCode, I.employerName, I.employerAddress, I.business, I.qstDisease, I.illness, I.illDuration, I.illTreatment, " +
                     "I.illhospital, I.DeclinedHistory, I.foreseeable, I.bloodPressureIllSystolic, I.bloodPressureIllDate, I.bloodPressureIllHospital, I.HyperlipidaemiaLevel, " +
                     "I.HyperlipidaemiaDate, I.HyperlipidaemiaHospital, I.height, I.weight, case I.isSmoker when 0 then 'Yes' else 'No' end as isSmoker, I.qstTravelFrequency, I.qstHazardous" +
                     " FROM  APPLICATION_INSURANCE AS A INNER JOIN" +
                     " APPLICATION_INSURANCE_INSURED AS I ON A.applicationID = I.applicationInsuranceId" +
                     " WHERE I.qstTravelFrequency <> null AND A.applicationID =@applicationID ";

        SqlCommand cmd = new SqlCommand(sql, con);
        cmd.Parameters.AddWithValue("@applicationID", applicationId);


        SqlDataAdapter myAdapter = new SqlDataAdapter(cmd);

        DataSet myDs = new DataSet();
        myAdapter.Fill(myDs, "TravelFrequency");

        return myDs;
    }

}